﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class DiscussThemetbDAO
    {
        /// <summary>
        /// 将讨论主题保存到数据库中
        /// </summary>
        /// <param name="dtTitle"></param>
        /// <param name="dtContent"></param>
        /// <param name="dtCreateTime"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public int SaveDiscussThemeInfo(string dtTitle, string dtContent, DateTime dtCreateTime, int uid, int daid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into DiscussTheme values(@dtTitle, @dtContent, @dtCreateTime, default, default, @uid, @daid)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtTitle", dtTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@dtContent", dtContent));
                sqlCmd.Parameters.Add(new SqlParameter("@dtCreateTime", dtCreateTime));
                sqlCmd.Parameters.Add(new SqlParameter("@uid", uid));
                sqlCmd.Parameters.Add(new SqlParameter("@daid", daid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据主题编号查找主题信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public DiscussThemetb FindById(int id)
        {
            DiscussThemetb disTheme = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussTheme where DiscussThemeId = @Id";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@Id", id));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    DiscussAreaDAO daDAO = new DiscussAreaDAO();
                    disTheme = new DiscussThemetb();
                    sqlDr.Read();
                    disTheme.DiscussThemeId = (int)sqlDr["DiscussThemeId"];
                    disTheme.DiscussThemeTitle = (string)sqlDr["DiscussThemeTitle"];
                    disTheme.DiscussThemeContent = (string)sqlDr["DiscussThemeContent"];
                    disTheme.DiscussThemeCreateTime = (DateTime)sqlDr["DiscussThemeCreateTime"];
                    disTheme.DiscussThemeHot = (int)sqlDr["DiscussThemeHot"];
                    disTheme.DiscussAppCount = (int)sqlDr["DiscussThemeAppCount"];
                    disTheme.DiscussThemeAuthor = userDAO.FindByUserId((int)sqlDr["DiscussThemeAuthor"]);
                    disTheme.DiscussArea = daDAO.findById((int)sqlDr["DiscussAreaId"]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return disTheme;
        }

        /// <summary>
        /// 查找所有讨论主题
        /// </summary>
        /// <returns></returns>
        public List<DiscussThemetb> FindALL()
        {
            List<DiscussThemetb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussTheme order by DiscussThemeCreateTime desc";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<DiscussThemetb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        DiscussThemetb disTheme = new DiscussThemetb();
                        disTheme = new DiscussThemetb();
                        disTheme.DiscussThemeId = (int)sqlDr["DiscussThemeId"];
                        disTheme.DiscussThemeTitle = (string)sqlDr["DiscussThemeTitle"];
                        disTheme.DiscussThemeContent = (string)sqlDr["DiscussThemeContent"];
                        disTheme.DiscussThemeCreateTime = (DateTime)sqlDr["DiscussThemeCreateTime"];
                        disTheme.DiscussThemeHot = (int)sqlDr["DiscussThemeHot"];
                        disTheme.DiscussAppCount = (int)sqlDr["DiscussThemeAppCount"];
                        disTheme.DiscussThemeAuthor = userDAO.FindByUserId((int)sqlDr["DiscussThemeAuthor"]);
                        list.Add(disTheme);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据讨论分区编号查找该分区所有主题
        /// </summary>
        /// <param name="daid"></param>
        /// <returns></returns>
        public List<DiscussThemetb> findALLByDaId(int daid)
        {
            List<DiscussThemetb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussTheme where DiscussAreaId = @daid order by DiscussThemeCreateTime desc";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@daid", daid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<DiscussThemetb>();
                    UserDAO userDAO = new UserDAO();
                    DiscussAreaDAO daDAO = new DiscussAreaDAO();
                    while (sqlDr.Read())
                    {
                        DiscussThemetb disTheme = new DiscussThemetb();
                        disTheme = new DiscussThemetb();
                        disTheme.DiscussThemeId = (int)sqlDr["DiscussThemeId"];
                        disTheme.DiscussThemeTitle = (string)sqlDr["DiscussThemeTitle"];
                        disTheme.DiscussThemeContent = (string)sqlDr["DiscussThemeContent"];
                        disTheme.DiscussThemeCreateTime = (DateTime)sqlDr["DiscussThemeCreateTime"];
                        disTheme.DiscussThemeHot = (int)sqlDr["DiscussThemeHot"];
                        disTheme.DiscussAppCount = (int)sqlDr["DiscussThemeAppCount"];
                        disTheme.DiscussThemeAuthor = userDAO.FindByUserId((int)sqlDr["DiscussThemeAuthor"]);
                        disTheme.DiscussArea = daDAO.findById((int)sqlDr["DiscussAreaId"]);
                        list.Add(disTheme);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 为主题增加人气
        /// </summary>
        /// <param name="dtid"></param>
        /// <returns></returns>
        public int UpdateThemeHot(int dtid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update DiscussTheme set DiscussThemeHot = DiscussThemeHot + 1 where DiscussThemeId = @dtid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtid", dtid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 为主题增加回复数量
        /// </summary>
        /// <param name="dtid"></param>
        /// <returns></returns>
        public int UpdateThemeAppCount(int dtid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update DiscussTheme set DiscussThemeAppCount = DiscussThemeAppCount + 1 where DiscussThemeId = @dtid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtid", dtid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据主题编号删除主题
        /// </summary>
        /// <param name="dtid"></param>
        /// <returns></returns>
        public int DelThemeById(int dtid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from DiscussTheme where DiscussThemeId = @dtid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtid", dtid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }
    }
}
